<?php
// +----------------------------------------------------------------------
// | ThinkPHP [ WE CAN DO IT JUST THINK ]
// +----------------------------------------------------------------------
// | Copyright (c) 2006~2017 http://thinkphp.cn All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: 李邦琦 <1536549251@qq.com>
// +----------------------------------------------------------------------

namespace app\crm\model;
use think\Db;
use think\Model;
use think\Cache;

class Signcount extends Model
{
    /**
     * 获取公告列表
     * @param str $where <查询条件>
     * @param int $page <页码>
     * @param int $limit <每页显示条数>
     * @return array <日报列表信息>
     */
    public function getSignCountData($where,$page,$limit)
    {
        $field = "
            count(id) as count_num, username,
            sum(case when signout = '1' then 1 else 0 end) as sign_num,
            sum(case when signout = '1' AND signtime < '09:00:00' then 1 else 0 end) as sign_qualified,
            sum(case when signout = '2' then 1 else 0 end) as signout_num,
            sum(case when signout = '2' AND signtime > '18:00:00' then 1 else 0 end) as signout_qualified
        ";
        $count = Db::name('mob_sign')->field($field)->where($where)->group('username')->count();
        // 查询状态为1的用户数据 并且每页显示10条数据
        $signCountData = Db::name('mob_sign')->field($field)->where($where)->limit(($page-1)*$limit,$limit)->group('username')->select();
        // echo Db::getLastSql();exit;
        $users = Cache::get('users');
        foreach ($signCountData as &$val) {
            // 迟到次数
            $val['sign_unqualified'] = $val['sign_num'] - $val['sign_qualified'];
            // 早退次数
            $val['signout_unqualified'] = $val['signout_num'] - $val['signout_qualified'];
            // 签到合格率
            if ($val['sign_num'] > 0) {
                $val['sign_percentage'] = (round($val['sign_qualified']/$val['sign_num']*100)).'%';
            } else {
                $val['sign_percentage'] = "0%";
            }
            // 签退合格率
            if ($val['signout_num'] > 0) {
                $val['signout_percentage'] = (round($val['signout_qualified']/$val['signout_num']*100)).'%';
            } else {
                $val['signout_percentage'] = "0%";
            }
            // 考勤合格率
            if ($val['count_num'] > 0) {
                $val['count_percentage'] = (round(($val['sign_qualified']+$val['signout_qualified'])/$val['count_num']*100)).'%';
            } else {
                $val['count_percentage'] = "0%";
            }
            $val['username'] = $users[$val['username']]['cn_name'];
        }
        $rowsList = count($signCountData) ? $signCountData : false;
        $arrData["code"] = 0;
        $arrData["msg"] = '';
        $arrData["count"] = $count;
        $arrData["data"] = $rowsList;
        return $arrData;
    }



}